﻿Imports System.Globalization

Public Class oGeneratePO
    'Seperate PO:
    ' - Per warehouse
    ' - Per Vendor
    ' - Per 20 lines

    Public Function GeneratePO(dt As DataTable) As String
        Try
            'LEVEL1: -------------GET ALL RECORDS BY VENDOR------------------
            Dim dtDistinctVendor As DataTable
            dtDistinctVendor = dt.DefaultView.ToTable("BPCode", True, "BPCode").Copy
            For Each drVendor As DataRow In dtDistinctVendor.Rows
                Dim dtByVendor As DataTable = dt.Clone
                For Each dr As DataRow In dt.Select("BPCode='" + drVendor("BPCode").ToString() + "'")
                    dtByVendor.ImportRow(dr)
                Next

                'LEVEL2:--------------GET ALL RECORDS BY DATE---------------------
                Dim dtDistinctDate As DataTable = dt.Clone
                dtDistinctDate = dtByVendor.DefaultView.ToTable("PostingDate", True, "PostingDate").Copy
                For Each drDate As DataRow In dtDistinctDate.Rows
                    Dim dtByDate As DataTable = dt.Clone
                    For Each dr As DataRow In dtByVendor.Select("PostingDate='" + drDate("PostingDate").ToString() + "'")
                        dtByDate.ImportRow(dr)
                    Next

                    'LEVEL3:--------------GET ALL RECORDS BY VENDOR REF NO.------------
                    Dim dtDistinctVendorRefNo As DataTable = dt.Clone
                    dtDistinctVendorRefNo = dtByDate.DefaultView.ToTable("ReferenceNo", True, "ReferenceNo").Copy
                    For Each drVendorRefNo As DataRow In dtDistinctVendorRefNo.Rows
                        Dim dtByVendorRefNo As DataTable = dt.Clone
                        For Each dr As DataRow In dtByDate.Select("ReferenceNo='" + drVendorRefNo("ReferenceNo").ToString() + "'")
                            dtByVendorRefNo.ImportRow(dr)
                        Next

                        'LEVEL4:-------------GET ALL RECORDS BY WAREHOUSE------------------
                        Dim dtDistinctWhs As DataTable
                        dtDistinctWhs = dtByVendorRefNo.DefaultView.ToTable("Warehouse", True, "Warehouse")
                        For Each drWhs As DataRow In dtDistinctWhs.Rows
                            Dim dtByWarehouse As DataTable = dt.Clone
                            For Each dr As DataRow In dtByVendorRefNo.Select("Warehouse='" + drWhs("Warehouse").ToString() + "'")
                                dtByWarehouse.ImportRow(dr)
                            Next
                            '--------------INSERT INTO PO------------------
                            Dim i As Integer = 0
                            Dim HeaderID As Integer

                            HeaderID = InsertPOHeader(dtByWarehouse.Rows(0))
                            For Each dr As DataRow In dtByWarehouse.Rows
                                InsertPORow(dr, HeaderID)
                                i = i + 1
                                ''LEVEL5:--------------20 Lines per PO------------------
                                'If i > 20 Then
                                '    HeaderID = InsertPOHeader(dtByWarehouse.Rows(0))
                                '    i = 0
                                'End If
                            Next
                        Next
                    Next
                Next
            Next
            Return ""
        Catch ex As Exception
            Return ex.ToString
        End Try
    End Function
    Private Function InsertPOHeader(dr As DataRow) As Integer

        Dim ors As SAPbobsCOM.Recordset
        Dim str As String = " Insert into AIPOImpH (Token,VendorCode,VendorReferenceNo,Remarks,CreatedDate,ErrMsg,PostingDate) values("
        str = str + "'" + PublicVariable.Token + "',"
        str = str + "'" + dr("BPCode").ToString + "',"
        str = str + "'" + dr("ReferenceNo").ToString + "',"
        str = str + "'" + dr("Remarks").ToString + "',"
        str = str + "'" + Now.ToString("yyyy-MM-dd") + "','',"
        str = str + "'" + DateTime.ParseExact(dr("PostingDate").ToString, "yyyyMMdd", CultureInfo.InvariantCulture, Globalization.DateTimeStyles.None).ToString("yyyy-MM-dd") + "')"
        str = str + " select @@IDENTITY ID "

        ors = PublicVariable.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
        ors.DoQuery(str)
        ors.MoveFirst()
        If ors.RecordCount > 0 Then
            Return ors.Fields.Item("ID").Value
            MessageBox.Show(ors.Fields.Item("ID").Value)
        Else
            Return 0
        End If

    End Function
    Private Sub InsertPORow(dr As DataRow, ID As Integer)
        Dim ors As SAPbobsCOM.Recordset
        Dim str As String = "insert into AIPOImpL(ItemCode,Quantity,Warehouse,HeaderID) values("
        str = str + "'" + dr("ItemCode").ToString + "',"
        str = str + dr("Quantity").ToString + ","
        str = str + "'" + dr("Warehouse").ToString + "',"
        str = str + ID.ToString + ")"
        ors = PublicVariable.oCompany.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)
        ors.DoQuery(str)

    End Sub
End Class
